import sqlite3
from typing import Dict, Any, Optional

from matplotlib.table import table


# 如果想要类的对象支持with操作 ——什么是with操作？
# 需要一个特殊函数 __enter__
# __exit__  退出
class SQLiteDB:
    def __init__(self,db_path:str="test.db"):
      self.db_path = db_path
      self.connection: sqlite3.Connection = None
      self.cursor: sqlite3.Cursor = None
    def __enter__(self):
    # with
      self.connect()
      return self

    def __exit__(self, exc_type, exc_val, exc_tb):
      self.close()

    def connect(self)->None:
      try:
          self.connection = sqlite3.connect(self.db_path)
          self.connection.row_factory=sqlite3.Row # 从数据库中取值，每次一行
          self.cursor = self.connection.cursor()
          print(f"Connected to database{self.db_path}")
      except sqlite3.Error as e:
          raise RuntimeError(f"Error connecting to database: {e}") from e

    def close(self)->None:
       if self.connection:
          self.connection.commit()
          self.connection.close()
          self.connection = None
          print(f"Closed connection to database{self.db_path}")

    # 执行相应代码
    def execute(
            self,
            sql:str,
            params: tuple=(),
            commit:bool=False
    ) -> sqlite3.Cursor:
        try:
            self.cursor.execute(sql, params)
            if commit:
                self.connection.commit()
            return self.cursor
        except sqlite3.Error as e:
            self.connection.rollback()
            raise RuntimeError(f"SQL 执行错误：{e}:SQL:{sql}") from e

    def create_table(self, tabl_name:str,schema:str) -> None:
        '''
        Create a table in the database 创建数据表
        :param tabl_name: 表名
        :param schema: 表结构定义
        :return:
        '''
        sql=f"CREATE TABLE IF NOT EXISTS {tabl_name} ({schema})"
        self.execute(sql,commit=True)
        print(f"Table {tabl_name} created successfully")

    def insert(self,tabl_name:str,data:Dict[str,Any]) -> int:
        """
        插入单条记录
        :param tabl_name: 表名
        :param data: 字段名-值
        :return: 插入的 rowid
        """
        columns=','.join(data.keys())
        placeholders=','.join(['?'] * len(data))
        sql=f"INSERT INTO {tabl_name} ({columns}) Values ({placeholders})"
        self.execute(sql,tuple(data.values()),commit=False)
        self.connection.commit()
        return self.cursor.lastrowid

    def update(
            self,
            tabl_name:str,
            data:Dict[str,Any],
            condition: str,
            params: tuple=()
    ) :
        set_clause=','.join([f"{k}=?" for k in data.keys()])
        values=tuple(data.values())+params
        sql=f"UPDATE {table} SET {set_clause} WHERE {condition}"
        self.execute(sql,values,commit=True)
        return self.cursor.rowcount

    def delete(self,tabl_name:str,condition:str,params:tuple=()) -> int:
        sql=f"DELETE FROM {tabl_name} WHERE {condition}"
        self.execute(sql,params,commit=True)
        return self.cursor.rowcount

    def fetchone(self)-> Optional[Dict[str, Any]]:
        row=self.cursor.fetchone()
        return dict(row) if row else None
    def table_exists(self,tabl_name:str) -> bool:
        sql="""
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
        """
        self.execute(sql,(tabl_name,))
        return self.fetchone() is not None

